Print
Q:
We need, for database replication, a list of primary keys on all tables. Without these definitions an automatic replication job out of the microsoft sql server does not work. The replications are needed to publish the web database from one master to multiple client instances automatically.
A:
Applicable to Sitecore 5.3.1
All databases have the same structure in Sitecore.
Here is one example:
|
Table |
Index name |
Columns |
|
dbo.ClientData |
ndxID, Unique Non-clustered |
ID |
|
dbo.History |
ndxCreated, Non-Unique Non-clustered (!) |
Created |
|
dbo.IDTable |
ndxID, Unique Non-clustered |
ID |
|
dbo.Items |
ndxID, Unique Non-clustered |
ID |
|
dbo.Links |
ndxID, Unique Non-clustered |
ID |
|
dbo.Properties |
ndxID, Unique Non-clustered |
ID |
|
dbo.PublishQueue |
ndxID, Unique Non-clustered |
ID |
|
dbo.Shadows |
ndxProxyTarget, Non-Unique Non- clustered; (!) ndxShadowID, Non-Unique Non-clustered; (!) ndxTargetID, Non-Unique Non-clustered; (!)
|
ProxyID+TargetID ShadowID TargetID |
|
dbo.SharedFields |
ndxUnique, Unique Non-clustered |
ItemId+FieldId |
|
dbo.Tasks |
ndxID, Unique Non-clustered |
ID |
|
dbo.UnversionedFields |
ndxUnique, Unique Non-clustered |
ItemId+Language+FieldId |
|
dbo.VersionedFields |
ndxUnique, Unique Non-clustered |
ItemId+Language+Version+FieldId |
|
dbo.WorkflowHistory |
ndxID, Unique Non-clustered |
ID |
As you can see tables History and Shadows do not have unique indexes. So, I assume if you want to use replication you should add a new column e.g. GUID type and create unique index.
Note: all indexes in Sitecore are not clustered because of performance reasons and therefore the example, GUID type, is not a good candidate for clustered indexes.